package es.ats.codington.festival.daos;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Properties;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;


import es.ats.codington.festival.domain.Visitor;
import es.ats.codington.festival.interfaces.daos.IVisitorDAOImp;
import es.ats.codington.festival.mappers.VisitorMapper;


public class VisitorDAOImp implements IVisitorDAOImp{

	private JdbcTemplate jdbcTemplateObject;
	private Properties queryProperties;
	
	public VisitorDAOImp(JdbcTemplate jdbcTemplateObject,Properties queryProperties){
			this.jdbcTemplateObject = jdbcTemplateObject;
		    this.queryProperties = queryProperties;
		}


	//	This source code tries to establish a connection to a MySQL server
	//	and displays a message concerning the error if connection fails
	
	//Following method creates a connection and returns it
	
	
	 public int loginDAO(final Visitor visitor) {
				
		List<Visitor> visitors=jdbcTemplateObject.query(queryProperties.getProperty("login"),
				new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement statement) throws SQLException{
				statement.setString(1, visitor.getUserName());
				statement.setString(2, visitor.getPassword());
			}
			
		}
		,new VisitorMapper());
			if(visitors.get(0).getPassword().equals(visitor.getPassword())){
				return 1;//Login Correct
			}
			else{
				return 0;//Login Incorrect
			}

	}	

	

	public int insertVisitor(final Visitor visitor){
		int rs = 0;

					List<Visitor> visitors=jdbcTemplateObject.query(queryProperties.getProperty("validateUser"),
							new PreparedStatementSetter(){
						@Override
						public void setValues(PreparedStatement statement) throws SQLException{
							statement.setString(1, visitor.getUserName());
						}
						
					}
					,new VisitorMapper());

			
					
				if(visitors.size() == 0){

					rs=jdbcTemplateObject.update(queryProperties.getProperty("insertVisitor"), 
							new PreparedStatementSetter(){
						@Override
						public void setValues(PreparedStatement statement)
								throws SQLException {
							statement.setString(1,visitor.getFirstName());
							statement.setString(2,visitor.getLastName());
							statement.setString(3,visitor.getUserName());
							statement.setString(4,visitor.getEmail());
							statement.setString(5,visitor.getDni());
							statement.setString(6,visitor.getnPhone());
							statement.setString(7,visitor.getAddress());
							statement.setString(8,visitor.getPassword());
							statement.setBoolean(9,visitor.isAdmin());
						}
					});
				}
			return rs;
		}
//
//	public int deleteVisitor( final String visitor) {
//		int aRows = 0;
//		
//		try{
//
//			PreparedStatement statement = con.prepareStatement("delete from people where userName = ?");
//			statement.setString(1, visitor);
//
//			con.setAutoCommit(false);
//			
//			aRows = statement.executeUpdate();
//			con.commit();
//			
//		}catch (SQLException e){
//			System.out.println(" DeleteVisitor: "+e.getMessage());
//		}
//		
//		return aRows;
//	}
	
	public int updateVisitor(final Visitor uVisitor,final String userName) {
		int rs = 0;
		
			rs=jdbcTemplateObject.update(queryProperties.getProperty("updateVisitor"), 
					new PreparedStatementSetter(){
				@Override
				public void setValues(PreparedStatement statement)
						throws SQLException {							
					
			statement.setString(1,uVisitor.getFirstName());
			statement.setString(2,uVisitor.getLastName());
			statement.setString(3,uVisitor.getEmail());
			statement.setString(4,uVisitor.getDni());
			statement.setString(5,uVisitor.getnPhone());
			statement.setString(6,uVisitor.getAddress());
			statement.setString(7,userName);
				}
			});
		return rs;
	}
	
	public int updatePassword(final String newPassword, String oldPassword,final String userName) {
		int rs = 0;
		List<Visitor> visitors=jdbcTemplateObject.query(queryProperties.getProperty("validateUser"),
				new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement statement) throws SQLException{
				statement.setString(1, userName);
			}
			
		}
		,new VisitorMapper());
			if(visitors.get(0).getPassword().equals(oldPassword)){
				rs=jdbcTemplateObject.update(queryProperties.getProperty("updatePass"), 
					new PreparedStatementSetter(){
					@Override
					public void setValues(PreparedStatement statement)
							throws SQLException {
				statement.setString(1, newPassword);
				statement.setString(2, userName);
				
					}
				});
			}
					else{
				System.out.println("The password doesn't match");
			}
		return rs;
	}
	
	public int getVisitorId(String userName, Connection con) {
		int peopleId = 0;
		ResultSet rs;
		
		try{
			PreparedStatement statement = con.prepareStatement("select peopleId from people where userName=?");
			statement.setString(1, userName);
			rs = statement.executeQuery();
			rs.next();
			peopleId = rs.getInt("peopleId");
			
		}catch (SQLException e){
			System.out.println(" UpdateVisitor: "+e.getMessage());
		}
		
		return peopleId;
	}
	
	public int isAdmin (String userName, Connection con){
	int peopleAdmin = 0;
	ResultSet rs;
	
	try{
		PreparedStatement statement = con.prepareStatement("select isAdmind from people where userName=?");
		statement.setString(1, userName);
		rs = statement.executeQuery();
		rs.next();
		peopleAdmin= rs.getInt("isAdmind");
		
		}catch (SQLException e){
			System.out.println(" UpdateVisitor: "+e.getMessage());
		}
	return peopleAdmin;
	}
	
	public Visitor getVisitor(String userName, Connection con) {
		ResultSet rs;
		Visitor visitor = new Visitor();
		try{
			PreparedStatement statement = con.prepareStatement("select * from people where userName=?");
			statement.setString(1, userName);
			rs = statement.executeQuery();
			rs.next();
			visitor.setFirstName(rs.getString("firstName"));
			visitor.setLastName(rs.getString("lastName"));
			visitor.setUserName(rs.getString("userName"));
			visitor.setEmail(rs.getString("email"));
			visitor.setDni(rs.getString("dni"));
			visitor.setnPhone(rs.getString("nPhone"));
			visitor.setAddress(rs.getString("address"));
			visitor.setPassword(rs.getString("password"));
			visitor.setAdmin(rs.getBoolean("isAdmind"));	
		}catch (SQLException e){
			System.out.println(" UpdateVisitor: "+e.getMessage());
		}
		return visitor;
	}
}
	
	
